BEGIN TRANSACTION
GO
ALTER TABLE dbo.Accounts ADD
	Basic numeric(18, 2) NOT NULL CONSTRAINT DF_Accounts_Basic DEFAULT 0,
	Tax numeric(18, 2) NOT NULL CONSTRAINT DF_Accounts_Tax DEFAULT 0,
	Remain numeric(18, 2) NOT NULL CONSTRAINT DF_Accounts_Remain DEFAULT 0,
	OverHead numeric(18, 2) NOT NULL CONSTRAINT DF_Accounts_OverHead DEFAULT 0,
	Final numeric(18, 2) NOT NULL CONSTRAINT DF_Accounts_Final DEFAULT 0
GO
COMMIT

-------------------------------------------------------------------------------------

USE [BS7]
GO
/****** Object:  View [dbo].[VW_Vouchers]    Script Date: 07/27/2019 15:04:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[VW_Vouchers]
AS
SELECT     dbo.VoucherDetails.VoucherID, dbo.Voucher.VoucherDate, dbo.Voucher.VoucherNo, dbo.Voucher.SessionID, dbo.SessionInfo.SessionTitle, 
                      dbo.SessionInfo.SessionFrom, dbo.SessionInfo.SessionTo, dbo.SessionInfo.CurrentSession, dbo.Voucher.Remarks AS MasterRemarks, dbo.Voucher.ReadOnly, 
                      dbo.VoucherDetails.VoucherDetailID, dbo.VoucherDetails.AccountNo, dbo.Accounts.AccountTitle, dbo.VoucherDetails.Remarks, 
                      CASE WHEN (dbo.VoucherDetails.DrOrCr = N'Dr') THEN dbo.VoucherDetails.Amount ELSE - 1 * dbo.VoucherDetails.Amount END AS Amount, 
                      CASE WHEN (dbo.VoucherDetails.DrOrCr = N'Dr') THEN dbo.VoucherDetails.Amount ELSE 0 END AS Debit, CASE WHEN (dbo.VoucherDetails.DrOrCr = N'Cr') 
                      THEN dbo.VoucherDetails.Amount ELSE 0 END AS Credit, dbo.VoucherDetails.DrOrCr, dbo.Accounts.ParentAccount, dbo.Voucher.BankAccountID, 
                      Accounts_2.AccountTitle AS BankAccountTitle, Accounts_2.Remarks AS BankAddress, Accounts_2.CellNo AS BankCellNo, Accounts_2.Phone AS BankPhone, 
                      dbo.Voucher.LoginID, dbo.Employees.EmployeeName, dbo.Voucher.HostName, dbo.Voucher.EntryDateTime, dbo.VoucherDetails.Amount AS ActualAmount, 
                      dbo.Voucher.Amount AS VoucherAmount, dbo.Voucher.TotalAmount, dbo.Voucher.NetAmount, dbo.Voucher.Expense, dbo.Voucher.TotalAmount AS Expr1, 
                      dbo.Voucher.NetAmount AS Expr2, dbo.Voucher.Discount, dbo.Voucher.ModifyID, Employees_1.EmployeeName AS ModifyName, dbo.Voucher.ModifyHostName, 
                      dbo.Voucher.ModifyDateTime, dbo.VoucherDetails.ItemID, dbo.vw_Items.ItemCode, dbo.vw_Items.ItemName, dbo.vw_Items.ItemNameUrdu, dbo.vw_Items.UnitID, 
                      dbo.vw_Items.UnitName, dbo.vw_Items.UnitNameUrdu, dbo.VoucherDetails.Qty, dbo.VoucherDetails.Rate, dbo.VoucherDetails.Unit, dbo.vw_Items.GroupID, 
                      dbo.vw_Items.GroupName, dbo.Voucher.CustomerName, dbo.Accounts.Remarks AS AccountAddress, dbo.Accounts.CellNo, dbo.Accounts.Phone, 
                      dbo.Accounts.NameUrdu, dbo.Voucher.ReceiptNo, dbo.Voucher.DateDelivery, dbo.Voucher.Cash, dbo.Voucher.CustomerID, 
                      Accounts_1.AccountTitle AS CustomerIDName, Accounts_1.CellNo AS CustomerCellNo, Accounts_1.Phone AS CustomerPhone, 
                      Accounts_1.Remarks AS CustomerAddress, dbo.Voucher.ProjectCost, dbo.Voucher.RefVNo, dbo.VoucherDetails.ComboID
FROM         dbo.vw_Items RIGHT OUTER JOIN
                      dbo.VoucherDetails ON dbo.vw_Items.ItemID = dbo.VoucherDetails.ItemID LEFT OUTER JOIN
                      dbo.Accounts ON dbo.VoucherDetails.AccountNo = dbo.Accounts.AccountNo LEFT OUTER JOIN
                      dbo.Employees AS Employees_1 RIGHT OUTER JOIN
                      dbo.Accounts AS Accounts_2 RIGHT OUTER JOIN
                      dbo.Accounts AS Accounts_1 RIGHT OUTER JOIN
                      dbo.Voucher ON Accounts_1.AccountNo = dbo.Voucher.CustomerID ON Accounts_2.AccountNo = dbo.Voucher.BankAccountID ON 
                      Employees_1.EmployeeID = dbo.Voucher.ModifyID LEFT OUTER JOIN
                      dbo.Employees ON dbo.Voucher.LoginID = dbo.Employees.EmployeeID ON dbo.VoucherDetails.VoucherID = dbo.Voucher.VoucherID LEFT OUTER JOIN
                      dbo.SessionInfo ON dbo.Voucher.SessionID = dbo.SessionInfo.SessionID

-------------------------------------------------------------------------------------

USE [BS7]
GO
/****** Object:  View [dbo].[vw_VouchersDetail]    Script Date: 07/27/2019 15:04:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[vw_VouchersDetail]
AS
SELECT     dbo.VoucherDetails.VoucherID, dbo.Voucher.VoucherNo, dbo.Voucher.VoucherDate, dbo.VoucherDetails.AccountNo, dbo.Accounts.AccountTitle, 
                      dbo.SessionInfo.SessionTitle, dbo.VoucherDetails.Remarks, dbo.VoucherDetails.Amount AS Debit, 0 AS Credit, dbo.VoucherDetails.DrOrCr, 
                      dbo.VoucherDetails.VoucherDetailID, dbo.Voucher.Remarks AS MasterRemarks, dbo.Voucher.SessionID, dbo.Accounts.ParentAccount, dbo.Voucher.BankAccountID, 
                      ISNULL(dbo.Voucher.CustomerName, '') AS CustomerName, dbo.Voucher.Expense, dbo.Voucher.Discount, dbo.VoucherDetails.ItemID, dbo.VoucherDetails.Qty, 
                      dbo.VoucherDetails.Qty AS InQty, 0 AS OutQty, dbo.VoucherDetails.Rate, dbo.VoucherDetails.Unit, dbo.VoucherDetails.ComboID
FROM         dbo.VoucherDetails LEFT OUTER JOIN
                      dbo.Accounts ON dbo.VoucherDetails.AccountNo = dbo.Accounts.AccountNo LEFT OUTER JOIN
                      dbo.Voucher ON dbo.VoucherDetails.VoucherID = dbo.Voucher.VoucherID LEFT OUTER JOIN
                      dbo.SessionInfo ON dbo.Voucher.SessionID = dbo.SessionInfo.SessionID
WHERE     (dbo.VoucherDetails.DrOrCr = N'Dr')
UNION ALL
SELECT     VoucherDetails_1.VoucherID, Voucher_1.VoucherNo, Voucher_1.VoucherDate, VoucherDetails_1.AccountNo, Accounts_1.AccountTitle, SessionInfo_1.SessionTitle, 
                      VoucherDetails_1.Remarks, 0 AS Debit, VoucherDetails_1.Amount AS Credit, VoucherDetails_1.DrOrCr, VoucherDetails_1.VoucherDetailID, 
                      Voucher_1.Remarks AS MasterRemarks, Voucher_1.SessionID, Accounts_1.ParentAccount, Voucher_1.BankAccountID, ISNULL(Voucher_1.CustomerName, '') 
                      AS CustomerName, Voucher_1.Expense, Voucher_1.Discount, VoucherDetails_1.ItemID, VoucherDetails_1.Qty, 0 AS InQty, VoucherDetails_1.Qty AS OutQty, 
                      VoucherDetails_1.Rate, VoucherDetails_1.Unit, VoucherDetails_1.ComboID
FROM         dbo.VoucherDetails AS VoucherDetails_1 LEFT OUTER JOIN
                      dbo.Accounts AS Accounts_1 ON VoucherDetails_1.AccountNo = Accounts_1.AccountNo LEFT OUTER JOIN
                      dbo.Voucher AS Voucher_1 ON VoucherDetails_1.VoucherID = Voucher_1.VoucherID LEFT OUTER JOIN
                      dbo.SessionInfo AS SessionInfo_1 ON Voucher_1.SessionID = SessionInfo_1.SessionID
WHERE     (VoucherDetails_1.DrOrCr = N'Cr')

-------------------------------------------------------------------------------------------------

USE [BS7]
GO
/****** Object:  View [dbo].[vw_Projects]    Script Date: 07/27/2019 17:00:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vw_Projects]
AS
SELECT     dbo.Accounts.AccountNo, dbo.Accounts.AccountTitle, 0 AS OpenBal, dbo.Accounts.Basic AS Debit, 0 AS Credit, dbo.Accounts.AccountType, 
                      dbo.Accounts.AccountNo AS ParentAccoutn, dbo.Accounts.AccountTitle AS ParentAccountTitle, 1 AS Type
FROM         dbo.Accounts LEFT OUTER JOIN
                      dbo.Accounts AS Accounts_1 ON dbo.Accounts.ParentAccount = Accounts_1.AccountNo
UNION ALL
SELECT     Accounts_5.AccountNo, Accounts_5.AccountTitle, 0 AS OpenBal, Accounts_5.Tax AS Debit, 0 AS Credit, Accounts_5.AccountType, 
                      Accounts_5.AccountNo AS ParentAccoutn, Accounts_5.AccountTitle AS ParentAccountTitle, 2 AS Type
FROM         dbo.Accounts AS Accounts_5 LEFT OUTER JOIN
                      dbo.Accounts AS Accounts_1 ON Accounts_5.ParentAccount = Accounts_1.AccountNo
UNION ALL
SELECT     Accounts_4.AccountNo, Accounts_4.AccountTitle, 0 AS OpenBal, Accounts_4.Remain AS Debit, 0 AS Credit, Accounts_4.AccountType, 
                      Accounts_4.AccountNo AS ParentAccoutn, Accounts_4.AccountTitle AS ParentAccountTitle, 3 AS Type
FROM         dbo.Accounts AS Accounts_4 LEFT OUTER JOIN
                      dbo.Accounts AS Accounts_1 ON Accounts_4.ParentAccount = Accounts_1.AccountNo
UNION ALL
SELECT     Accounts_3.AccountNo, Accounts_3.AccountTitle, 0 AS OpenBal, Accounts_3.OverHead AS Debit, 0 AS Credit, Accounts_3.AccountType, 
                      Accounts_3.AccountNo AS ParentAccoutn, Accounts_3.AccountTitle AS ParentAccountTitle, 4 AS Type
FROM         dbo.Accounts AS Accounts_3 LEFT OUTER JOIN
                      dbo.Accounts AS Accounts_1 ON Accounts_3.ParentAccount = Accounts_1.AccountNo
UNION ALL
SELECT     Accounts_2.AccountNo, Accounts_2.AccountTitle, 0 AS OpenBal, Accounts_2.Final AS Debit, 0 AS Credit, Accounts_2.AccountType, 
                      Accounts_2.AccountNo AS ParentAccoutn, Accounts_2.AccountTitle AS ParentAccountTitle, 5 AS Type
FROM         dbo.Accounts AS Accounts_2 LEFT OUTER JOIN
                      dbo.Accounts AS Accounts_1 ON Accounts_2.ParentAccount = Accounts_1.AccountNo

GO